﻿/*
'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.
'Description:外委类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2020-4-20
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using System.Collections;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESSynergicInfoBusiness
    {
        uMESCommonBusiness common = new uMESCommonBusiness();

        public DataTable GetEmployeeInfoNew(Dictionary<string, string> para)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine("SELECT re.id,re.rolename,re.roleid,re.employeeid,e.fullname,t.teamname,f.factoryname, f.description,e.employeename ");
            strSql.AppendLine("FROM roleemployee re");
            strSql.AppendLine("LEFT JOIN employee  e ON e.employeeid = re.employeeid");
            strSql.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
            strSql.AppendLine("LEFT JOIN factory f ON sv.factoryid = f.factoryid");
            strSql.AppendLine("LEFT JOIN team t ON t.teamid = e.teamid");
            strSql.AppendLine("WHERE 1 = 1");
            if (para.ContainsKey("RoleName"))
            {
                strSql.Append("AND re.rolename LIKE '%" + para["RoleName"] + "%'");
            }

            DataTable dt = OracleHelper.GetDataTable(strSql.ToString());
            return dt;
        }

        //获取互检人信息
        public DataTable GetEmployeeInfo(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT r.rolename,e.*
                        FROM roledef r
                        LEFT JOIN employeerole er ON er.roleid = r.roleid
                        LEFT JOIN employee e ON e.employeeid = er.employeeid
                        WHERE 1 =1
                        ");
            if (para.ContainsKey("RoleName"))
            {
                sb.Append("AND r.rolename LIKE '%" + para["RoleName"] + "%'");
            }

            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        public DataTable GetContainerReportInfo(Dictionary<string, string> para)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT w.*
                        FROM workreportinfo w
                        WHERE 1 = 1");
            //批次ID
            if (para.ContainsKey("ContainerID"))
            {
                sb.Append(string.Format("AND w.containerid = '{0}'", para["ContainerID"]));
            }

            //工艺规程ID
            if (para.ContainsKey("WorkflowId"))
            {
                sb.Append(string.Format("AND w.workflowid = '{0}'", para["WorkflowId"]));
            }
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 获取待转出的数据,两种数据：一种自动外委的，另一种临时申请到待转出的
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetSynergicOutData(Dictionary<string, string> para)
        {
            string strSql = @"  SELECT m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,c.qty,c.plannedstartdate,c.plannedcompletiondate ,
            c.containerid,ws.workflowid,c.uomid,NULL synergicinfoid,NULL preposerdate ,NULL tofactoryid,c.productid,sc.synergiccompanyname,s.unitworktime,ws.sequence,s.specid,nvl2(sti.id,'是','否') MoveConfirm
            FROM container c 
            LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid
            LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid
            LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
            LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
            LEFT JOIN product p ON p.productid=c.productid
            LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
            LEFT JOIN synergicinfo st ON st.containerid=c.containerid AND st.workflowid=ws.workflowid AND st.specid = s.specid 
            left join operation o on o.operationid=s.operationid
            left join synergiccompany sc on sc.synergiccompanyid=o.synergiccompanyid
            left join submittostockinfo sti on sti.containerid=c.containerid and s.specid=sti.specid and sti.workflowid=ws.workflowid and sti.isfinished=0
            WHERE c.parentcontainerid IS NULL AND c.status<>0  AND s.issynergic =1 AND c.containerid <> c.containername 
            AND st.submitdate IS NULL AND c.finishstate IS NULL ";

            string condition = "";

            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                condition += string.Format(" AND LOWER(m.ProcessNo) like '%{0}%'", para["ProcessNo"].ToLower());
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                condition += string.Format(" AND LOWER(pb.ProductName) like '%{0}%'", para["ProductName"].ToLower());
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                condition += string.Format(" AND c.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                condition += string.Format(" AND c.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                condition += string.Format(" AND LOWER(c.containername) like '%{0}%' ", para["ContainerName"].ToLower());
            }

            if (para.ContainsKey("ScanContainerName") && !string.IsNullOrWhiteSpace(para["ScanContainerName"]))
            {
                condition += string.Format(" and c.containername='{0}' ", para["ScanContainerName"]);
            }

            strSql += condition;

            strSql += " UNION ALL ";

            //手动申请到待转出的
            strSql += @"SELECT m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,
                        c.qty,c.plannedstartdate,c.plannedcompletiondate ,c.containerid,ws.workflowid,c.uomid,st.synergicinfoid,st.preposerdate,st.tofactoryid,c.productid,
                        sc.synergiccompanyname,s.unitworktime,ws.sequence,s.specid,nvl2(sti.id,'是','否') MoveConfirm FROM synergicinfo st
                        LEFT JOIN container c ON c.containerid=st.containerid 
                        LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid
                        LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                        LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                        LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid
                        LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid
                        LEFT JOIN product p ON p.productid=c.productid
                        LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                        left join synergiccompany sc on sc.synergiccompanyid=st.tofactoryid
                        left join submittostockinfo sti on sti.containerid=c.containerid and s.specid=sti.specid and sti.workflowid=ws.workflowid and sti.isfinished=0
                        WHERE st.isuse=0 AND st.status=5 AND st.submitdate IS NULL AND ws.workflowid=st.workflowid AND c.parentcontainerid IS NULL AND c.status<>0
                         AND c.finishstate IS NULL AND s.specid = st.specid";
            strSql += condition;
            strSql = "(" + strSql + ")";
            strSql += " ORDER BY plannedstartdate ASC ";
            uMESPagingDataDTO result = new uMESPagingDataDTO();
            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }

        /// <summary>
        /// 获取待转出的数据,两种数据：一种自动外委的，另一种临时申请到待转出的
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetSynergicOutDataOld(Dictionary<string, string> para)
        {
            string strSql = @"select m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,c.qty,c.plannedstartdate,c.plannedcompletiondate,
                                c.containerid,w.workflowid,c.uomid,null synergicinfoid,null preposerdate ,null tofactoryid
                                from container c
                                left join mfgorder m on m.mfgorderid=c.mfgorderid
                                left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                                left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
                                left join workflow w on w.workflowid=ws.workflowid
                                left join product p on p.productid=c.productid
                                left join productbase pb on pb.productbaseid=p.productbaseid
                                left join SynergicInfo st on st.containerid=c.containerid and st.workflowid=w.workflowid and st.submitdate is null and st.isuse=0
                                inner join (select w.workflowid from workflow w
                                left join workflowstep ws on ws.workflowid=w.workflowid
                                left join specbase sb on sb.specbaseid=ws.specbaseid
                                left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                                where s.issynergic=1 group by w.workflowid
                                ) a on a.workflowid=w.workflowid where c.parentcontainerid is null and c.status<>0 and st.synergicinfoid is null ";
            string condition = "";

            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                condition += string.Format(" and m.ProcessNo like '%{0}%'", para["ProcessNo"]);
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                condition += string.Format(" and pb.ProductName like '%{0}%'", para["ProductName"]);
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                condition += string.Format(" and c.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                condition += string.Format(" and c.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                condition += string.Format(" and c.containername like '%{0}%' ", para["ContainerName"]);
            }

            if (para.ContainsKey("ScanContainerName") && !string.IsNullOrWhiteSpace(para["ScanContainerName"]))
            {
                condition += string.Format(" and c.containername='{0}' ", para["ScanContainerName"]);
            }

            strSql += condition;

            strSql += " union all ";

            //手动申请到待转出的
            strSql += @"select m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,
                        c.qty,c.plannedstartdate,c.plannedcompletiondate ,c.containerid,ws.workflowid,c.uomid,st.synergicinfoid,st.preposerdate,st.tofactoryid  from synergicinfo st
                        left join container c on c.containerid=st.containerid 
                        left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                        left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
                        left join mfgorder m on m.mfgorderid=c.mfgorderid
                        left join product p on p.productid=c.productid
                        left join productbase pb on pb.productbaseid=p.productbaseid
                        where st.isuse=0 and st.status=5 and st.submitdate is null and ws.workflowid=st.workflowid and c.parentcontainerid is null and c.status<>0";

            strSql += condition;
            strSql = "(" + strSql + ")";
            strSql += " order by plannedstartdate asc ";
            uMESPagingDataDTO result = new uMESPagingDataDTO();
            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }

        /// <summary>
        /// 获取需要外委的工序
        /// </summary>
        /// <param name="workflowId"></param>
        /// <returns></returns>
        public DataTable GetSynergicSpec(string workflowId)
        {
            string strSql = string.Format(@"select w.workflowid, sb2.specname,s.specid,ws.sequence,s.unitworktime,s.setupworktime 
                                            from workflow w
                                            left join workflowstep ws on ws.workflowid=w.workflowid
                                            left join specbase sb on sb.specbaseid=ws.specbaseid
                                            left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                                            left join specbase sb2 on sb2.specbaseid=s.specbaseid
                                            where w.workflowid='{0}' and s.issynergic=1 ", workflowId);
            strSql += " order by ws.sequence asc ";
            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 保存外协转出数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel SaveSynergicInfoOut(Dictionary<string, string> para)
        {
            string strSql = @"insert into SynergicInfo st (st.synergicinfoid,st.synergicinfoname,st.containerid,st.synergictype,st.fromfactoryid,st.specid,st.tofactoryid,st.plannedcomplitiondate,
            st.qty,st.submitdate,st.submitemployeeid,st.submitnotes,st.uomid,st.workflowid,st.status,st.outhandoveremp,ReturnSpecID,isuse)
            values('{0}','{1}','{2}',1,'{3}','{4}','{5}',to_date('{6}','yyyy-mm-dd'),'{7}',sysdate,'{8}','{9}','{10}','{11}',{12},'{13}','{14}',0)";
            double maxNo = GetMaxSerialNum(DateTime.Now.Year.ToString());
            maxNo++;
            string maxSerialNo = DateTime.Now.Year.ToString() + maxNo.ToString().PadLeft(8, '0');
            string sql = string.Format(strSql, Guid.NewGuid().ToString(), maxSerialNo, para["ContainerId"], para["FromFactoryId"],
                para["SpecId"], para["TofactryId"], para["PlannedComplitionDate"], para["Qty"],
                para["SubmitEmployeeId"], para["SubmitNotes"], para["UomId"], para["WorkflowId"],
                para["Status"], para["OuthandoverEmp"], para["ReturnSpecID"]);
            int count = OracleHelper.ExecuteSql(sql);
            if (count > 0)
            {
                return new ResultModel(true, "外协转出成功");
            }
            else
            {
                return new ResultModel(false, "外协转出失败");
            }
        }

        /// <summary>
        /// 获取最大流水号
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public int GetMaxSerialNum(string year)
        {
            string strSql = @"select count(st.synergicinfoname) from synergicinfo st where substr(st.synergicinfoname,0,4)=" + year;
            var maxNo = Convert.ToInt32(OracleHelper.GetSingle(strSql));
            return maxNo;
        }

        /// <summary>
        /// 获取待转回的数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetSynergicInData(Dictionary<string, string> para)
        {
            string strSql = @"select m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,c.qty,
                            c.plannedstartdate,c.plannedcompletiondate ,c.containerid,w.workflowid,c.uomid,sb.specname OutSpecName,
                            sb2.specname InSpecName,st.ReturnSpecId,sc.synergiccompanyname,st.PlannedComplitionDate,e.fullname OutEmpoyeeFullName,
                            st.synergicinfoid,st.qty AS outQty ,c.productid
                            from container c left join mfgorder m on m.mfgorderid=c.mfgorderid
                            left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                            left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
                            left join workflow w on w.workflowid=ws.workflowid
                            left join product p on p.productid=c.productid
                            left join productbase pb on pb.productbaseid=p.productbaseid
                            left join SynergicInfo st on st.containerid=c.containerid and st.workflowid=w.workflowid and st.submitdate is not null and st.returndate is null and st.isuse=0
                            left join spec s on s.specid=st.specid
                            left join specbase sb on sb.specbaseid=s.specbaseid--转出工序
                            left join spec s2 on s2.specid=st.returnspecid
                            left join specbase sb2 on sb2.specbaseid=s2.specbaseid--转入工序
                            left join SynergicCompany sc on sc.synergiccompanyid=st.tofactoryid
                            left join employee e on e.employeeid=st.SubmitEmployeeID
                            where c.parentcontainerid is null and c.status<>0 and st.synergicinfoid is not null and c.containerid <> c.containername ";

            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += string.Format(" and lower(m.ProcessNo) like '%{0}%'", para["ProcessNo"].ToLower());
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                strSql += string.Format(" and lower(pb.ProductName) like '%{0}%'", para["ProductName"].ToLower());
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                strSql += string.Format(" and c.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                strSql += string.Format(" and c.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += string.Format(" and lower(c.containername) like '%{0}%' ", para["ContainerName"].ToLower());
            }

            if (para.ContainsKey("ScanContainerName") && !string.IsNullOrWhiteSpace(para["ScanContainerName"]))
            {
                strSql += string.Format(" and c.containername='{0}' ", para["ScanContainerName"]);
            }

            strSql += " order by c.plannedstartdate asc ";
            uMESPagingDataDTO result = new uMESPagingDataDTO();
            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }

        /// <summary>
        /// 保存转回记录
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel SaveSynergicIn(Dictionary<string, string> para)
        {
            ResultModel result = new ResultModel(false, "");

            string strSql = @"update synergicinfo st set st.ReturnQty={0},st.ReturnDate=sysdate,st.ReturnEmployeeID='{1}',st.ReturnNotes='{2}',st.InHandoverEmp='{3}',st.ReinspectionEmp='{4}'";

            strSql = string.Format(strSql, para["ReturnQty"], para["ReturnEmployeeId"], para["ReturnNotes"], para["InHandoverEmp"], para["ReinspectionEmp"]);

            strSql += " where st.synergicinfoid = '{0}'";
            strSql = string.Format(strSql, para["SynergicinfoId"]);

            ArrayList excuteSql = new ArrayList();

            excuteSql.Add(strSql);
            //存入报工表记录
            strSql = @"insert into WorkReportInfo wr (wr.workreportinfoid,wr.workreportinfoname,wr.containerid,wr.specid,wr.qty,wr.uomid,wr.reportdate,wr.reporttype,wr.synergicinfoid,
                       wr.workflowid,workflowstepid,FactoryID) values('{0}', '{1}', '{2}', '{3}',{4},'{5}',sysdate,{6},'{7}','{8}','{9}','{10}') ";
            strSql = string.Format(strSql, Guid.NewGuid().ToString(), para["WorkreportinfoName"], para["ContainerId"], para["SpecId"], para["ReturnQty"], para["UomId"],
                para["ReportType"], para["SynergicinfoId"], para["WorkflowId"], common.GetStepInfo(para["WorkflowId"], para["SpecId"]).Rows[0]["WorkflowStepID"].ToString(),
                para["FactoryID"]);
            excuteSql.Add(strSql);
            OracleHelper.ExecuteSqlTran(excuteSql);
            result.IsSuccess = true;
            return result;
        }

        /// <summary>
        /// 获取可以转工申请的批次
        /// </summary>
        /// <returns></returns>
        public uMESPagingDataDTO GetCanApplySynergicData(Dictionary<string, string> para)
        {
            string strSql = @"select m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,c.qty,
                            c.plannedstartdate,c.plannedcompletiondate,c.containerid,w.workflowid,c.uomid ,ws.sequence,c.productid
                            from container c left join mfgorder m on m.mfgorderid=c.mfgorderid
                            left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                            left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
                            left join workflow w on w.workflowid=ws.workflowid
                            left join product p on p.productid=c.productid
                            left join productbase pb on pb.productbaseid=p.productbaseid
                            left join SynergicInfo st on st.containerid=c.containerid and st.workflowid=w.workflowid and st.Returndate is null and st.isuse=0--没有转回的记录
                            where c.parentcontainerid is null and c.status<>0 and st.synergicinfoid is null  and c.finishstate is null";

            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += string.Format(" and m.ProcessNo like '%{0}%'", para["ProcessNo"]);
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                strSql += string.Format(" and pb.ProductName like '%{0}%'", para["ProductName"]);
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                strSql += string.Format(" and c.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                strSql += string.Format(" and c.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += string.Format(" and c.containername like '%{0}%' ", para["ContainerName"]);
            }

            if (para.ContainsKey("ScanContainerName") && !string.IsNullOrWhiteSpace(para["ScanContainerName"]))
            {
                strSql += string.Format(" and c.containername='{0}' ", para["ScanContainerName"]);
            }

            strSql += " order by c.plannedstartdate asc ";

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }

        /// <summary>
        /// 保存转工申请信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel InsertSynergicApplyInfo(Dictionary<string, string> para)
        {
            string id = "";
            if (para.ContainsKey("SynergicInfoID"))
                id = para["SynergicInfoID"];
            else
                id = Guid.NewGuid().ToString();

            double maxNo = GetMaxSerialNum(DateTime.Now.Year.ToString());
            maxNo++;
            string maxSerialNo = DateTime.Now.Year.ToString() + maxNo.ToString().PadLeft(8, '0');
            string strSql = @"insert into SynergicInfo st (st.synergicinfoid,st.synergicinfoname,st.containerid,st.synergictype,st.fromfactoryid,st.specid,
                            st.qty,st.uomid,st.workflowid,st.status,ReturnSpecID,preposerdate,proposerid,isuse,sequence,returnsequence)
                            values('{0}','{1}','{2}',1,'{3}','{4}','{5}','{6}','{7}',{8},'{9}',sysdate,'{10}',0,{11},{12})";
            strSql = string.Format(strSql, id, maxNo, para["ContainerId"], para["FromFactoryId"], para["SpecId"], para["Qty"], para["UomId"],
                para["WorkflowId"], para["Status"], para["ReturnSpecID"], para["ProposerId"], para["Sequence"], para["ReturnSequence"]);
            OracleHelper.ExecuteSql(strSql);
            return new ResultModel(true, "");
        }

        /// <summary>
        /// 获取转工审核的批次
        /// </summary>
        /// <returns></returns>
        public uMESPagingDataDTO GetSynergicAuditData(Dictionary<string, string> para)
        {
            string strSql = @"select m.mfgordername,m.processno,m.oprno,c.containername,pb.productname,p.description productdesc,c.qty,c.plannedstartdate,
            c.plannedcompletiondate,c.containerid,w.workflowid,c.uomid,st.synergicinfoid,st.proposerid,st.preposerdate,c.productid,sc.synergiccompanyname
            from container c left join mfgorder m on m.mfgorderid=c.mfgorderid
            left join currentstatus cu on cu.currentstatusid=c.currentstatusid
            left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
            left join workflow w on w.workflowid=ws.workflowid
            left join product p on p.productid=c.productid
            left join productbase pb on pb.productbaseid=p.productbaseid
            left join SynergicInfo st on st.containerid=c.containerid and st.workflowid=w.workflowid and st.preposerdate is not null and st.isuse=0--申请的记录
            left join synergiccompany sc on sc.synergiccompanyid=st.tofactoryid
             where c.parentcontainerid is null and c.status<>0 ";

            if (para.ContainsKey("Status") && !string.IsNullOrWhiteSpace(para["Status"]))
            {
                strSql += string.Format(" and st.Status ={0}", para["Status"]);
            }

            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += string.Format(" and m.ProcessNo like '%{0}%'", para["ProcessNo"]);
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                strSql += string.Format(" and pb.ProductName like '%{0}%'", para["ProductName"]);
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                strSql += string.Format(" and c.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                strSql += string.Format(" and c.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            if (para.ContainsKey("ContainerName") && !string.IsNullOrWhiteSpace(para["ContainerName"]))
            {
                strSql += string.Format(" and c.containername like '%{0}%' ", para["ContainerName"]);
            }

            if (para.ContainsKey("ScanContainerName") && !string.IsNullOrWhiteSpace(para["ScanContainerName"]))
            {
                strSql += string.Format(" and c.containername='{0}' ", para["ScanContainerName"]);
            }

            strSql += " order by c.plannedstartdate asc ";
            uMESPagingDataDTO result = new uMESPagingDataDTO();
            result = OracleHelper.GetPagingDataIns(strSql, int.Parse(para["CurrentPageIndex"]), int.Parse(para["PageSize"]));
            return result;
        }

        /// <summary>
        /// 获取已经申请的工序
        /// </summary>
        /// <param name="synergicinfoid"></param>
        /// <returns></returns>
        public DataTable GetSynergicApplySpec(string synergicinfoid)
        {
            string strSql = string.Format(@"select st.workflowid,ws.sequence startseq,ws2.sequence endseq ,s.unitworktime,s.setupworktime 
                                            from  synergicinfo st 
                                            left join spec s on s.specid=st.specid
                                            left join specbase sb on sb.specbaseid=s.specbaseid
                                            left join workflowstep ws on ws.workflowid=st.workflowid and (ws.specbaseid=sb.specbaseid or ws.specid=s.specid)
                                            left join spec s2 on s2.specid=st.returnspecid
                                            left join specbase sb2 on sb2.specbaseid=s2.specbaseid
                                            left join workflowstep ws2 on ws2.workflowid=st.workflowid and (ws2.specbaseid=sb2.specbaseid or ws2.specid=s2.specid)
                                            where st.synergicinfoid='{0}'", synergicinfoid);
            //查询起始结束顺序号
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            if (dt.Rows.Count == 0)
            {
                return new DataTable();
            }
            //查询起始结束之间的所有工序
            strSql = string.Format(@"select w.workflowid,ws.sequence,s.specid,sb2.specname,s.unitworktime,s.setupworktime 
			                        from workflow w
                                    left join workflowstep ws on ws.workflowid=w.workflowid
                                    left join specbase sb on sb.specbaseid=ws.specbaseid
                                    left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                                    left join specbase sb2 on sb2.specbaseid=s.specbaseid
                                    where w.workflowid='{0}' and ws.sequence>={1} and ws.sequence<={2}", dt.Rows[0]["workflowid"].ToString(), dt.Rows[0]["startseq"], dt.Rows[0]["endseq"]);

            dt = OracleHelper.Query(strSql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据工艺获取工序信息,可以申请临时外协的
        /// </summary>
        /// <param name="workflowId"></param>
        /// <returns></returns>
        public DataTable GetStepInfo(Dictionary<string, string> para)
        {
            string strSql = @"select w.workflowid,ws.sequence,ws.workflowstepname,ws.workflowstepid,s.specid,sb2.specname,s.unitworktime,s.setupworktime  
                                from workflow w 
                                left join workflowstep ws on ws.workflowid=w.workflowid
                                left join specbase sb on sb.specbaseid=ws.specbaseid
                                left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
                                left join specbase sb2 on sb2.specbaseid=s.specbaseid";

            strSql += string.Format(" where w.workflowid='{0}' and (s.issynergic=0 or s.issynergic is null ) ", para["WorkflowId"]);
            if (para.ContainsKey("SpecList"))
            {
                strSql += string.Format("and s.specid not in ({0})", para["SpecList"]);
            }

            if (para.ContainsKey("Sequence"))
            {
                strSql += string.Format("and ws.sequence >={0}", para["Sequence"]);
            }
            strSql += " order by ws.sequence asc ";
            return OracleHelper.Query(strSql).Tables[0];
        }
    }
}
